LockEdits Property Example

This example demonstrates pessimistic locking by setting the LockEdits property to True, and then demonstrates optimistic locking by setting the LockEdits property to False. It also demonstrates what kind of error handling is required in a multiuser database environment in order to modify a field. The PessimisticLock and OptimisticLock functions are required for this procedure to run.

Sub LockEditsX()

   Dim dbsNorthwind As Database
   Dim rstCustomers As Recordset
   Dim strOldName As String

   Set dbsNorthwind = OpenDatabase("Northwind.mdb")
   Set rstCustomers = _
      dbsNorthwind.OpenRecordset("Customers", _
      dbOpenDynaset)

   With rstCustomers
      ' Store original data.
      strOldName = !CompanyName

      If MsgBox("Pessimistic locking demonstration...", _
            vbOKCancel) = vbOK Then

         ' Attempt to modify data with pessimistic locking 
         ' in effect.
         If PessimisticLock(rstCustomers, !CompanyName, _
               "Acme Foods") Then
            MsgBox "Record successfully edited."

            ' Restore original data...
            .Edit
            !CompanyName = strOldName
            .Update
         End If

      End If

      If MsgBox("Optimistic locking demonstration...", _
            vbOKCancel) = vbOK Then

         ' Attempt to modify data with optimistic locking 
         ' in effect.
         If OptimisticLock(rstCustomers, !CompanyName, _
               "Acme Foods") Then
            MsgBox "Record successfully edited."

            ' Restore original data...
            .Edit
            !CompanyName = strOldName
            .Update
         End If

      End If

      .Close
   End With

   dbsNorthwind.Close

End Sub

Function PessimisticLock(rstTemp As Recordset, _
   fldTemp As Field, strNew As String) As Boolean

   dim ErrLoop as Error

   PessimisticLock = True

   With rstTemp
      .LockEdits = True

      ' When you set LockEdits to True, you trap for errors 
      ' when you call the Edit method.
      On Error GoTo Err_Lock
      .Edit
      On Error GoTo 0

      ' If the Edit is still in progress, then no errors
      ' were triggered; you may modify the data.
      If .EditMode = dbEditInProgress Then
         fldTemp = strNew
         .Update
         .Bookmark = .LastModified
      Else
         ' Retrieve current record to see changes made by
         ' other user.
         .Move 0
      End If

   End With
   
   Exit Function
   
Err_Lock:

   If DBEngine.Errors.Count > 0 Then
      ' Enumerate the Errors collection.
      For Each errLoop In DBEngine.Errors
         MsgBox "Error number: " & errLoop.Number & _
            vbCr & errLoop.Description
      Next errLoop
      PessimisticLock = False
   End If
   
   Resume Next

End Function

Function OptimisticLock(rstTemp As Recordset, _
   fldTemp As Field, strNew As String) As Boolean

   dim ErrLoop as Error

   OptimisticLock = True

   With rstTemp
      .LockEdits = False
      .Edit
      fldTemp = strNew

      ' When you set LockEdits to False, you trap for errors 
      ' when you call the Update method.
      On Error GoTo Err_Lock
      .Update
      On Error GoTo 0

      ' If there is no Edit in progress, then no errors were
      ' triggered; you may modify the data.
      If .EditMode = dbEditNone Then
         ' Move current record pointer to the most recently
         ' modified record.
         .Bookmark = .LastModified
      Else
         .CancelUpdate
         ' Retrieve current record to see changes made by
         ' other user.
         .Move 0
      End If

   End With
   
   Exit Function
   
Err_Lock:

   If DBEngine.Errors.Count > 0 Then
      ' Enumerate the Errors collection.
      For Each errLoop In DBEngine.Errors
         MsgBox "Error number: " & errLoop.Number & _
            vbCr & errLoop.Description
      Next errLoop
      OptimisticLock = False
   End If
   
   Resume Next

End Function